/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package management;

import java.sql.*;
import java.util.Date;
import java.io.File;
import javax.swing.JOptionPane;

/**
*
* @author Cucnth
*
**/

public class DataAccess
{
    private String host;
    private int port;
    private String database;
    private String user;
    private String pass;
    private Connection con;
    private ResultSet rs;
    private Statement cmd;

    public void setHost(String host)
    {
        this.host = host;
    }

    public void setPort(int port)
    {
        this.port = port;
    }

    public void setDatabase(String database)
    {
        this.database = database;
    }

    public void setUser(String user)
    {
        this.user = user;
    }

    public void setPass(String pass)
    {
        this.pass = pass;
    }

    public DataAccess(String host, int port, String database, String user, String pass)
    {
        this.host = host;
        this.port = port;
        this.database = database;
        this.user = user;
        this.pass = pass;
    }

    public DataAccess(String host, String database, String user, String pass)
    {
        this(host, 1433, database, user, pass);
    }

    public DataAccess() throws Exception
    {
        loadConfig();
    }

    public void driverTest() throws Exception
    {
        try
        {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    public Connection getConnection() throws Exception
    {
        try
        {
            if(con == null)
            {
                driverTest();

                con = DriverManager.getConnection("jdbc:sqlserver://" + host + ":" + port + "; databaseName = " + database + "; integratedSecurity = false; user = " + user + "; password = " + pass);
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }

        return con;
    }

    public Statement getStatement() throws Exception
    {
        try
        {
            if(cmd == null || cmd.isClosed())
            {
                cmd = this.getConnection().createStatement();
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }

        return cmd;
    }

    public ResultSet executeQuery(String sql) throws Exception
    {
        try
        {
            rs = this.getStatement().executeQuery(sql);
        }
        catch(Exception ex)
        {
            throw ex;
        }

        return rs;
    }

    public boolean executeUpdate(String sql) throws Exception
    {
        try
        {
            return this.getStatement().executeUpdate(sql) > 0;
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            this.closeCon();
        }
    }

    public ResultSet executeQuery(String[] columns, String table, String condition, String order) throws Exception
    {
        try
        {
            String sql = "Select ";

            if(columns != null)
            {
                for(String column : columns)
                {
                    sql += column + ", ";
                }

                sql = sql.substring(0, sql.length() - 2);
            }
            else
            {
                sql += "*";
            }
            
            sql += " From [" + table + "]";

            if(condition != null)
            {
                sql += " Where " + condition;
            }

            if(order != null)
            {
                sql += " Order By " + order;
            }

            return this.executeQuery(sql);
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    public ResultSet executeQuery(String[] columns, String table, String condition) throws Exception
    {
        try
        {
            return this.executeQuery(columns, table, condition, null);
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    public ResultSet executeQuery(String table, String condition, String order) throws Exception
    {
        try
        {
            return this.executeQuery(null, table, condition, order);
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    public ResultSet executeQuery(String table, String condition) throws Exception
    {
        try
        {
            return this.executeQuery(table, condition, null);
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    public boolean executeProcedureUpdate(String sql, Object[] object) throws Exception
    {
        try
        {
            CallableStatement call = this.getConnection().prepareCall(sql);

            for(int i = 0; i < object.length; i++)
            {
                try
                {
                    call.setObject(i + 1, object[i]);
                }
                catch(Exception ex)
                {    
                    try
                    {
                        call.setDate(i + 1, new java.sql.Date(((Date)object[i]).getTime()));                    
                    }
                    catch(Exception e)
                    {
                        throw e;
                    }
                }
            }

            return call.executeUpdate() > 0;
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            this.closeCon();
        }
    }

    public ResultSet executeProcedureQuery(String sql, Object[] object) throws Exception
    {
        try
        {
            CallableStatement call = this.getConnection().prepareCall(sql);

            for(int i = 0; i < object.length; i++)
            {
                call.setObject(i + 1, object[i]);
            }

            rs = call.executeQuery();
        }
        catch(Exception ex)
        {
            throw ex;
        }

        return rs;
    }
    
    public boolean checkExists(String table, String column, String condition) throws Exception
    {
        try
        {
            String sql = "";
            
            if(condition != null)
            {
                sql = "Select Count(" + column + ") From [" + table + "] Where " + condition;
            }
            else
            {
                sql = "Select Count(" + column + ") From [" + table + "]";
            }
            
            rs = this.executeQuery(sql);
            rs.next();

            return rs.getInt(1) > 0;
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            this.closeCon();
        }
    }

    public boolean checkExists(String table, String column, String[] columns, String[] values, String[] columnsCon, String[] valuesCon) throws Exception
    {
        try
        {
            String condition = "";

            if(columns != null && values != null)
            {
                if(columns.length != values.length)
                {
                    throw new Exception("Columns length not equals values length!");
                }

                for(int i = 0; i < columns.length; i++)
                {
                    condition += columns[i] + " = '" + values[i] + "' And ";
                }
            }
            else
            {
                throw new Exception("You must enter parameter of columns and values!");
            }

            if(columnsCon != null && valuesCon != null)
            {
                if(columnsCon.length != valuesCon.length)
                {
                    throw new Exception("ColumnsCon length not equals valuesCon length!");
                }

                for(int i = 0; i < columnsCon.length; i++)
                {
                    condition += columnsCon[i] + " <> '" + valuesCon[i] + "' And ";
                }
            }

            condition = condition.substring(0, condition.length() - " And ".length());

            return this.checkExists(table, column, condition);
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    public boolean checkExists(String table, String column, String[] columns, String[] values) throws Exception
    {
        try
        {
            return this.checkExists(table, column, columns, values, null, null);
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    public int getMMID(String table, String column, boolean isMax) throws Exception
    {
        try
        {
            rs = this.executeQuery("Select " + (isMax ? "Max" : "Min") + "(" + column + ") From [" + table + "]");
            rs.next();

            return rs.getInt(1);            
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            this.closeCon();
        }
    }
    
    public int getTotal(String column, String table, String condition) throws Exception
    {
        try
        {
            String sql = "Select Count(" + column + ") From [" + table + "]" + (condition == null ? "" : " Where " + condition);
            rs = this.executeQuery(sql);
            
            if(rs.next())
            {
                return rs.getInt(1);
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            this.closeCon();
        }
    }
    
    public int getTotal(String column, String table) throws Exception
    {
        try
        {
            return getTotal(column, table, null);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public int getTotalStatus(String column, String table, String columnStatus, boolean status, String condition) throws Exception
    {
        try
        {
            String sql = "Select Count(" + column + ") From [" + table + "] Where " + columnStatus + " = " + (status ? 1 : 0) + (condition == null ? "" : " And " + condition);
            rs = this.executeQuery(sql);
            
            if(rs.next())
            {
                return rs.getInt(1);
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            this.closeCon();
        }
    }
    
    public int getTotalStatus(String column, String table, String columnStatus, boolean status) throws Exception
    {
        try
        {
            return this.getTotalStatus(column, table, columnStatus, status, null);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public int getTotalDate(String column, String table, String date, String condition) throws Exception
    {
        try
        {
            String sql = "Select Count(" + column + ") From [" + table + "] Where convert(varchar(30), " + date + ", 101) = convert(varchar(30), getDate(), 101)" + (condition == null ? "" : " And " + condition);
            rs = this.executeQuery(sql);
            
            if(rs.next())
            {
                return rs.getInt(1);
            }
            else
            {
                return 0;
            }            
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            this.closeCon();
        }
    }
    
    public int getTotalDate(String column, String table, String date) throws Exception
    {
        try
        {
            return this.getTotalDate(column, table, date, null);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    
    public void closeCon() throws Exception
    {
        try
        {
            if(rs != null && !rs.isClosed())
            {
                rs.close();
                rs = null;
            }

            if(cmd != null)
            {
                cmd.close();
                cmd = null;
            }

            if(con != null && !con.isClosed())
            {
                con.close();
                con = null;
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    private void loadConfig() throws Exception
    {
        try
        {
            String path = new File("").getAbsolutePath() + "\\config.txt";

            if(CheckForm.checkFileExist(path, false))
            {
                String[] config = CheckForm.readFromFile(path).split("\t");

                this.host = config[0];
                this.port = Integer.parseInt(config[1]);
                this.database = config[2];
                this.user = config[3];
                this.pass = config[4];
            }
            else
            {
                throw new Exception("File config not found!");
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }
}
